<?php
declare(strict_types=1);

namespace App\Controller\Admin\V1\Paper;

use App\Controller\Admin\AdminController;
use App\Helper\Utils;
use App\Job\InputQuestionJob;
use App\Model\Area;
use App\Model\Community;
use App\Model\ExportFile;
use App\Model\PaperQuestion;
use App\Model\Street;
use App\Model\UserPaper;
use App\Model\UserPaperAnswer;
use App\Repositories\CommunityRepository;
use App\Repositories\StreetRepository;
use App\Repositories\UserPaperAnswerRepository;
use App\Repositories\UserRepository;
use App\Services\QueueService;
use Hyperf\DbConnection\Db;
use Hyperf\Di\Annotation\Inject;

class StaticsController extends AdminController{

    /**
     * @Inject
     * @var QueueService
     */
    public $queue_service;

    public function answerRightDistrict(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $cloumns=[
            'district_id',
            DB::raw('sum(all_score) as all_score'),
            DB::raw('sum(question_num) as question_num'),
            DB::raw('sum(all_score)/sum(question_num) as ratio')
        ];
        $districts = Area::query()->where('parent_id',223)->get()->keyBy('id')->toArray();
        $query= UserPaper::query()->where('paper_id',$paper_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('district_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('created_at','>=',$start_time)
                ->where('created_at','<=',$end_time);
        }
        $result = $query->get();
        if($result){
            foreach ($result as &$res){
                $res->district_name = $districts[$res->district_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function answerRightStreet(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $cloumns=[
            'street_id',
            DB::raw('sum(all_score) as all_score'),
            DB::raw('sum(question_num) as question_num'),
            DB::raw('sum(all_score)/sum(question_num) as ratio')
        ];
        $streets = Street::query()->get()->keyBy('id')->toArray();
        $query = UserPaper::query()->where('paper_id',$paper_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('street_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('created_at','>=',$start_time)
                ->where('created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->street_name = $streets[$res->street_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function answerRightCommunity(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $cloumns=[
            'community_id',
            DB::raw('sum(all_score) as all_score'),
            DB::raw('sum(question_num) as question_num'),
            DB::raw('sum(all_score)/sum(question_num) as ratio')
        ];
        $communities = Community::query()->get()->keyBy('id')->toArray();
        $query = UserPaper::query()->where('paper_id',$paper_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('community_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('created_at','>=',$start_time)
                ->where('created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->community_name = $communities[$res->community_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function degreeDistrict(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $question = PaperQuestion::query()->where('paper_id',$paper_id)->where('score','>',0)->orderBy('listorder','desc')
            ->orderBy('id','desc')->first();
        if(!$question){
            return $this->response->success([]);
        }
        $districts = Area::query()->where('parent_id',223)->get()->keyBy('id')->toArray();
        $cloumns=[
            'district_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query= UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id');
        })->where('user_paper_answer.paper_id',$paper_id)->where('question_id',$question->id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('district_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->district_name = $districts[$res->district_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function degreeStreet(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $question = PaperQuestion::query()->where('paper_id',$paper_id)->where('score','>',0)->orderBy('listorder','desc')
            ->orderBy('id','desc')->first();
        if(!$question){
            return $this->response->success([]);
        }
        $streets = Street::query()->get()->keyBy('id')->toArray();
        $cloumns=[
            'street_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query = UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id');
        })->where('user_paper_answer.paper_id',$paper_id)->where('question_id',$question->id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('street_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result = $query->get();
        if($result){
            foreach ($result as &$res){
                $res->street_name = $streets[$res->street_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function degreeCommunity(){
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $question = PaperQuestion::query()->where('paper_id',$paper_id)->where('score','>',0)->orderBy('listorder','desc')
            ->orderBy('id','desc')->first();
//        if(!$question){
//            return $this->response->success([]);
//        }
        $communities = Community::query()->get()->keyBy('id')->toArray();
        $cloumns=[
            'community_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query = UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id');
        })->where('user_paper_answer.paper_id',$paper_id)->where('question_id',object_get($question,'id',0))
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('community_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result = $query->get();
        if($result){
            foreach ($result as &$res){
                $res->community_name = $communities[$res->community_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function questionRightDistrict(){
        $question_id = $this->request->input('question_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $districts = Area::query()->where('parent_id',223)->get()->keyBy('id')->toArray();
        $cloumns=[
            'district_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query = UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id')
            ->whereNull('user_paper.deleted_at');
        })->where('question_id',$question_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('district_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->district_name = $districts[$res->district_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function questionRightStreet(){
        $question_id = $this->request->input('question_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $streets = Street::query()->get()->keyBy('id')->toArray();
        $cloumns=[
            'street_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query = UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id');
        })->where('question_id',$question_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('street_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->street_name = $streets[$res->street_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function questionRightCommunity(){
        $question_id = $this->request->input('question_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $communities = Community::query()->get()->keyBy('id')->toArray();
        $cloumns=[
            'community_id',
            DB::raw('count(user_paper_id) as count_num'),
            DB::raw('sum(is_answer_right) as right_num'),
            DB::raw('sum(is_answer_right)/count(user_paper_id) as ratio')
        ];
        $query = UserPaperAnswer::query()->join('user_paper',function($join){
            $join->on('user_paper_answer.user_paper_id','=','user_paper.id')
            ->whereNull('user_paper.deleted_at');
        })->join('community',function($join){
            $join->on('user_paper.community_id','=','community.id')->whereNull('community.deleted_at');
        })->where('question_id',$question_id)
            ->select($cloumns)
            ->orderBy('ratio','desc')
            ->groupBy('community_id');
        if($start_time!=''&&$end_time!=''&&$end_time>$start_time){
            $query->where('user_paper_answer.created_at','>=',$start_time)
                ->where('user_paper_answer.created_at','<=',$end_time);
        }
        $result=$query->get();
        if($result){
            foreach ($result as &$res){
                $res->community_name = $communities[$res->community_id]['name'];
                $res->show_ratio = sprintf('%.2f',$res->ratio*100);
            }
        }
        return $this->response->success($result);
    }

    public function inputQuestion(){
        $rows = $this->request->input('rows',10);
        $paper_id = $this->request->input('paper_id',0);
        $start_time = $this->request->input('start_time','');
        $end_time = $this->request->input('end_time','');
        $columns=[
            'user_paper_id',
            'user_paper.paper_id',
            'user_paper_answer.id',
            'answer_txt',
            'district_id',
            'street_id',
            'community_id',
            'user_id'
        ];
        $search=[
            'paper_id'=>[
                'field' => 'user_paper_answer.paper_id',
                'value' => $paper_id
            ],
            'question_type' => [
                'field' => 'question_type',
                'value' => PaperQuestion::TYPE_INPUT
            ]
        ];
        if($start_time!=''&&$end_time!=''){
            $search['created_at_start']=[
                'field' => 'user_paper_answer.created_at',
                'value' => $start_time,
                'method'=>'>='
            ];
            $search['created_at_end']=[
                'field' => 'user_paper_answer.created_at',
                'value' => $end_time,
                'method'=>'<='
            ];
        }
        $join['user_paper']=[
            'on' => ['user_paper_answer.user_paper_id','=','user_paper.id'],
            'where'=>[
                'deleted_at'=>[
                    'field' => 'user_paper.deleted_at',
                    'value'=>'',
                    'method'=>'null'
                ]
            ]
        ];
        $orderBy= [
            [
                'field' => 'user_paper_answer.id',
                'sort' => 'desc'
            ]
        ];
        $districts = Area::query()->where('parent_id',223)->get()->keyBy('id')->toArray();
        $datas = UserPaperAnswerRepository::getInstance()->getList($columns,$search,['limit'=>$rows],$orderBy,$join);
        $street_ids=[];
        $community_ids = [];
        if(!empty($datas['result'])){
            foreach ($datas['result'] as &$item){
                $item['user'] = UserRepository::getInstance()->getBaseInfo($item['user_id']);
                $item['district'] = isset($districts[$item['district_id']])?$districts[$item['district_id']]:null;
                if($item['street_id']){
                    if(isset($street_ids[$item['street_id']])){
                        $item['street'] = $street_ids[$item['street_id']];
                    }else{
                        $street = StreetRepository::getInstance()->one(['id','name'],[[
                            'field' => 'id',
                            'value' => $item['street_id']
                        ]]);
                        $street_ids[$item['street_id']] = $street;
                        $item['street'] = $street;
                    }
                }
                if($item['community_id']){
                    if(isset($community_ids[$item['community_id']])){
                        $item['community'] = $community_ids[$item['community_id']];
                    }else{
                        $community = CommunityRepository::getInstance()->one(['id','name'],[[
                            'field' => 'id',
                            'value' => $item['community_id']
                        ]]);
                        $community_ids[$item['community_id']] = $community;
                        $item['community'] = $community;
                    }
                }
            }
        }
        return $this->response->success($datas);
    }

    public function inputQuestionExport(){
        $admin = $this->request->getAttribute('admin');
        $params = $this->request->all();
        $hash_id = $params['hash_id'];
        $exists = ExportFile::query()->where('hash_id',$hash_id)->first();
        if(!$exists){
            $newname = Utils::createFileName().'.csv';
            $dir = '/uploads/export/'.date('Ym').'/'.date('d').'/';
            $folder = BASE_PATH.'/public'.$dir;
            if (!is_dir($folder)) {
                mkdir($folder, 0777, true);
            }
            $model = new ExportFile();
            $model->hash_id=$hash_id;
            $model->file_url = $dir.$newname;
            $model->type = 'thread';
            $model->params = json_encode($params);
            if(!$model->save()){
                return $this->response->error(10050,'创建导出失败');
            }
            $params['file_url'] = $dir.$newname;
            cache()->set('export_'.$hash_id,0);
            $this->queue_service->push(new InputQuestionJob($params));
            return $this->response->success([
                'hash_id' => $hash_id,
                'progress' => 0,
                'file_url' => config('common.image_prefix').$dir.$newname
            ]);
        }
        return $this->response->success([
            'hash_id' => $hash_id,
            'progress' => cache()->get('export_'.$hash_id),
            'file_url' => config('common.image_prefix').$exists->file_url
        ]);
    }

}